/**
 * 
 */
package data;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

import edu.ipfw.fitness.EquipmentCategoryType;
import edu.ipfw.fitness.EquipmentReportDetailsType;
import edu.ipfw.fitness.EquipmentWorkoutListType;
import edu.ipfw.fitness.EquipmentWorkoutType;
import edu.ipfw.fitness.MembershipDurationType;
import edu.ipfw.fitness.MembershipType;
import edu.ipfw.fitness.PatronWorkoutListType;
import edu.ipfw.fitness.PatronWorkoutType;
import edu.ipfw.services.training.PatronNotFound;
import edu.ipfw.services.training.PatronNotFoundFault;

/**
 * @author Angie
 *
 */
public class DbTraining {
	Connection conn = null;
	
	public DbTraining() {
		connectDb();

	
	}
	
	public void closeDbConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			// handle any errors
			e.printStackTrace();
		}
	}
	private void connectDb() {
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/DWWIG_Project", "root", "admin");

		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param patronId
	 * @return
	 * @throws PatronNotFoundFault 
	 */
	public int getPatron(String patronId) throws PatronNotFoundFault {
		// TODO Auto-generated method stub
		String sql = "Select id from patron where patronId = '" + patronId + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
			do {	
				return rs.getInt(1);
			
				
			}while(rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;
	}

	/**
	 * @param patron
	 * @param workoutDateTime
	 */
	public boolean createPatronWorkout(int patron,
			XMLGregorianCalendar workoutDateTime) {
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "select * from pworkout where patronId = '" + patron +"' and workoutDate = '" 
		+ df1.format(workoutDateTime.toGregorianCalendar().getTime()) + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
				return true;
			} else {
				sql = "Insert into pworkout (patronId,workoutDate) values ('" + patron + "', '" 
				+ df1.format(workoutDateTime.toGregorianCalendar().getTime()) + "')";	
			s.execute(sql);
			return true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}
	public int getPatronWorkout(int patron, XMLGregorianCalendar workoutDateTime) throws PatronNotFoundFault {
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "Select id from pworkout where patronId ='" + patron + "' and workoutDate = '" 
		+ df1.format(workoutDateTime.toGregorianCalendar().getTime()) + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
			do {	
				return rs.getInt(1);
			
				
			}while(rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;
	}
	
	private void throwPatronNotFoundFault() throws PatronNotFoundFault {
		PatronNotFound error = new PatronNotFound();
		error.setPatronNotFoundFault("Patron was not found.");
		throw new PatronNotFoundFault("PatronNotFound", error);
	}

	/**
	 * @param equipmentId
	 * @return
	 * @throws PatronNotFoundFault 
	 */
	public int getEquipment(String equipmentId) throws PatronNotFoundFault {
		// TODO Auto-generated method stub
		String sql = "Select id from equipment where equipmentId = '" + equipmentId + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
			do {	
				return rs.getInt(1);
			
				
			}while(rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;	}

	/**
	 * @param workout
	 * @param equipment
	 * @return
	 */
	public boolean beginEquipmentWorkout(int workout, int equipment) {
		System.out.println("workoutID: " + workout);
		System.out.println("workoutID: " + equipment);
		// TODO Auto-generated method stub
		String sql = "insert into eworkout (pworkoutId,equipmentId) values('" + workout + "', '" + equipment + "')";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			s.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * @param patron
	 * @param equipment
	 * @param workoutDateTime
	 * @return
	 * @throws PatronNotFoundFault 
	 */
	public int getWorkout(int equipment,
			int pworkout) throws PatronNotFoundFault {
		// TODO Auto-generated method stub
		String sql = "Select id from eworkout where pworkoutId ='" + pworkout  + "' and equipmentId = '" + equipment + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
			do {	
				return rs.getInt(1);
			
				
			}while(rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;
	}

	/**
	 * @param workout
	 * @param equipmentWorkout
	 * @return
	 */
	public boolean updateWorkout(int workout,
			EquipmentWorkoutType equipmentWorkout) {
		// TODO Auto-generated method stub
		List<EquipmentReportDetailsType> workouts = equipmentWorkout.getEquipmentWorkoutDetails();
		for (EquipmentReportDetailsType e : workouts) {
		String sql = "insert into wdetails(eworkoutid,distance,speed,level,duration) values ('" + workout + "', '" 
		+ e.getDistance() + "', '" + e.getSpeed() + "', '" + e.getLevel() + "', '" + e.getDuration().getHours() + ":" + e.getDuration().getMinutes() + ":" + e.getDuration().getSeconds() + "')" ;
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			s.execute(sql);
			
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
			return false;
		}
		}
		return true;
		
		
	}

	/**
	 * @param workout
	 * @param equipmentId
	 * @return
	 */
	public EquipmentWorkoutType getWorkoutDetails(int workout,
			String equipmentId) {
		// TODO Auto-generated method stub
		EquipmentWorkoutType ew = new EquipmentWorkoutType();
		ew.setEquipmentId(equipmentId);
		String sql = "Select * from wdetails where eworkoutid = '" + workout + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
			do {	
				EquipmentReportDetailsType edt = new EquipmentReportDetailsType();
				edt.setDistance(rs.getBigDecimal("distance"));
				edt.setLevel(BigInteger.valueOf(rs.getInt("level")));
				edt.setSpeed(rs.getBigDecimal("speed"));
				edt.setDuration(DatatypeFactory.newInstance().newDuration(rs.getTime("duration").getTime()));
				ew.getEquipmentWorkoutDetails().add(edt);
			}while(rs.next());
			} else {
				
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (DatatypeConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ew;
	}

	/**
	 * @param patron
	 * @param startDateTime
	 * @param endDateTime
	 * @return
	 */
	public PatronWorkoutListType getPatronWorkoutHistory(int patron,
			XMLGregorianCalendar startDateTime, XMLGregorianCalendar endDateTime, String patronId) {
		PatronWorkoutListType p = new PatronWorkoutListType();
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "Select * from pworkout where patronId = '" + patron + "' and workoutDate between '" 
		+ df1.format(startDateTime.toGregorianCalendar().getTime()) + "' and '" + df1.format(endDateTime.toGregorianCalendar().getTime()) + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
		while (rs.next()) {
			PatronWorkoutType pw = new PatronWorkoutType();
			pw.setPatronId(patronId);
			GregorianCalendar c = new GregorianCalendar();
			c.setTime(rs.getDate("workoutDate"));
			pw.setWorkoutDate(DatatypeFactory.newInstance().newXMLGregorianCalendar(c));
			String sql2 = "Select * from eworkout,equipment where eworkout.equipmentid = equipment.id and pworkoutId = '" + rs.getInt("id") + "'";
			Statement s2 = conn.createStatement();
			ResultSet rs2 = s2.executeQuery(sql2);
			while (rs2.next()) {
				EquipmentWorkoutType ew = new EquipmentWorkoutType();
				ew.setEquipmentId(rs2.getString("equipment.equipmentid"));
				String sql3 = "Select * from wdetails where eworkoutid = '" + rs2.getInt("eworkout.id") + "'";
				Statement s3 = conn.createStatement();
				ResultSet rs3 = s3.executeQuery(sql3);
				while (rs3.next()) {
					EquipmentReportDetailsType edt = new EquipmentReportDetailsType();
					edt.setDistance(rs3.getBigDecimal("distance"));
					edt.setLevel(BigInteger.valueOf(rs3.getInt("level")));
					edt.setSpeed(rs3.getBigDecimal("speed"));
					edt.setDuration(DatatypeFactory.newInstance().newDuration(rs3.getTime("duration").getTime()));
					ew.getEquipmentWorkoutDetails().add(edt);
				
				}
				pw.getEquipmentWorkout().add(ew);
			}
			p.getList().add(pw);
		}
		
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (DatatypeConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}

	/**
	 * @param patronId
	 * @param patron
	 * @param equipmentCategory
	 * @param startDateTime
	 * @param endDateTime
	 * @return
	 */
	public EquipmentWorkoutListType getEquipmentWorkoutHistory(int patron, EquipmentCategoryType equipmentCategory,
			XMLGregorianCalendar startDateTime, XMLGregorianCalendar endDateTime) {
		// TODO Auto-generated method stub
		EquipmentWorkoutListType ewl = new EquipmentWorkoutListType();
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "Select * from pworkout where patronId = '" + patron + "' and workoutDate between '" 
		+ df1.format(startDateTime.toGregorianCalendar().getTime()) + "' and '" + df1.format(endDateTime.toGregorianCalendar().getTime()) + "'";
		if (conn == null) {
			connectDb();
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
		while (rs.next()) {
			
			GregorianCalendar c = new GregorianCalendar();
			c.setTime(rs.getDate("workoutDate"));
			String sql2 = "Select * from eworkout,equipment where eworkout.equipmentid = equipment.id and pworkoutId = '" + rs.getInt("id") 
			+ "' and equipment.categoryid = (select cid from ecategories where ecategory = '" + equipmentCategory + "')";
			Statement s2 = conn.createStatement();
			ResultSet rs2 = s2.executeQuery(sql2);
			while (rs2.next()) {
				EquipmentWorkoutType ew = new EquipmentWorkoutType();
				ew.setEquipmentId(rs2.getString("equipment.equipmentid"));
				String sql3 = "Select * from wdetails where eworkoutid = '" + rs2.getInt("eworkout.id") + "'";
				Statement s3 = conn.createStatement();
				ResultSet rs3 = s3.executeQuery(sql3);
				while (rs3.next()) {
					EquipmentReportDetailsType edt = new EquipmentReportDetailsType();
					edt.setDistance(rs3.getBigDecimal("distance"));
					edt.setLevel(BigInteger.valueOf(rs3.getInt("level")));
					edt.setSpeed(rs3.getBigDecimal("speed"));
					edt.setDuration(DatatypeFactory.newInstance().newDuration(rs3.getTime("duration").getTime()));
					ew.getEquipmentWorkoutDetails().add(edt);
				
				}
				ewl.getList().add(ew);
			}
		}
		
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (DatatypeConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ewl;
	}
}
